/**
 * 
 */
package org.swing.utility.jdbc.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

/**
 * @author lqnhu
 *
 */
public class JdbcExcute {  
    public Connection getCon(){  
        Connection conn = null;  
        try {  
              
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/libraryv1?useUnicode=true&characterEncoding=utf-8&useOldAliasMetadataBehavior=true";  
            conn = DriverManager.getConnection(url, "root", "123");
            conn.setAutoCommit(false);  
        } catch (ClassNotFoundException e) {  
            e.printStackTrace();  
        }catch (SQLException e) {  
            e.printStackTrace();  
        }  
          return conn;  
    }  
    
    public ArrayList selQuery(String sqlstr){  
        Connection connetcion=null;  
        Statement stm=null;  
        try {  
            connetcion = getCon();  
            connetcion.setAutoCommit(true);  
        } catch (Exception e1) {  
            e1.printStackTrace();  
        }  
        ArrayList datalist = new ArrayList();  
        ResultSet rs = null;  
        if(sqlstr.equals(""))  
            return null;  
        try {  
            stm=connetcion.createStatement();  
            rs = stm.executeQuery(sqlstr);  
            ResultSetMetaData rsmd = rs.getMetaData();  
            int colum=rsmd.getColumnCount();  
            //HashMap map = getDataColl(rs);  
            //Object[] keys = map.keySet().toArray();  
            while(rs.next()){  
                HashMap dataSet = new HashMap();  
                for(int i=1;i<=colum;i++){  
                    String strFieldName = rsmd.getColumnName(i).toLowerCase();  
                    // if(keys[i].toString().equals("COLUMN_NAME"))keys[i]="Field";  
                    // if(keys[i].toString().equals("COLUMN_TYPE"))keys[i]="Type";  
                    // if(keys[i].toString().equals("IS_NULLABLE"))keys[i]="Null";  
                    // if(keys[i].toString().equals("COLUMN_KEY"))keys[i]="Key";  
                    // if(keys[i].toString().equals("COLUMN_DEFAULT"))keys[i]="Default";  
                    // if(keys[i].toString().equals("EXTRA"))keys[i]="Extra";  
                     dataSet.put(strFieldName, rs.getObject(i));  
                }  
                datalist.add(dataSet);  
                  
            }  
        } catch (Exception e) {  
            e.printStackTrace();  
        }finally{  
            try {  
                if(stm!=null)stm.close();  
                if(connetcion!=null)connetcion.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  
          
        return datalist;  
    }  
      
    public int saveOrUpdate(String sqlstr){  
        Connection connetcion=null;  
        try {  
            connetcion = getCon();  
            connetcion.setAutoCommit(true);  
        } catch (Exception e1) {  
            e1.printStackTrace();  
        }  
        try {  
            connetcion.createStatement().execute(sqlstr);  
            return 0;  
        } catch (Exception e) {  
            e.printStackTrace();  
            return -1;  
        }finally{  
            try {  
                if(!connetcion.isClosed()){  
                    connetcion.close();  
                }  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  
    }  
      
    public static void main(String[] args) {  
        JdbcExcute dbExcute=new JdbcExcute();  
        System.out.println(dbExcute.selQuery("SELECT * FROM reader"));  
    }  
} 
